﻿
--获取等待职位设定的员工
CREATE PROCEDURE [dbo].[proc_Employee_GetNoSetList]
(
	@startIndex nvarchar(50),
	@endIndex nvarchar(50),
	@docount bit,
	@username nvarchar (50),
	@ename nvarchar(50),
	@set nvarchar(4),
	@audit nvarchar(4),
	@cid nvarchar(400)
)
as
declare @sql nvarchar (4000)
DECLARE @te NVARCHAR(400)
SET @te=''
IF(@cid<>'0')
SET @te=@te+'e.companyid in ('+@cid+')'
ELSE
SET @te=@te+'e.id<>0 and e.companyid is not null'
BEGIN				
	set @sql =
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable(nid)'
		+ ' select ee.id from employee e inner join employee_entry ee on e.id=ee.eid'
	    + ' where '
		+  @te
		+ '	and e.ename like ''%'+@ename+'%'''
		+ '	and (case (isnull(ee.tdepartmentid,0)) when 0 then 0 else 1 end) like ''%'+@set+'%'''
		+ '	and (case when ee.audit=1 then 1 ' 
		+ '		 when ee.audit=0 and ee.auditperson is null then 0'
		+ '		 when ee.audit=0 and ee.auditperson is not null then 2 end) like ''%'+@audit+'%''' 
		+ '	and  e.state <> 5'
		+ ' order by e.id asc'
		+ ' select' 
		+ ' EE.Id,'
		+ ' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+ ' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+ ' EE.EId,'
		+ ' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+ ' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+ ' (select Birthday from Employee E where EE.EId=E.Id) as Birthday,'
		+ ' (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as EducationName,'
		+ ' (select IDCard from Employee E where EE.EId=E.Id) as IDCard,'
		+ ' EE.TDepartmentId as DepartmentId,'
		+ ' (select COUNT(0) from @indextable) as coun,'
		+ ' EE.audit,'
		+ 'EE.auditPerson,'
		+ 'Proposer,'
		+ '(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName'

		+ ',EE.GID from employee_entry EE'
		+ '	inner join @indextable t on'
		+ ' EE.id=t.nid	 '
		+ 'where t.id between '+ @startIndex +' and '+ @endIndex 
		+ ' order by t.id'	
	END

EXEC(@sql)
set nocount on


----获取等待职位设定的员工
--ALTER PROCEDURE [dbo].[proc_Employee_GetNoSetList]
--(
--	@startIndex int,
--	@endIndex int,
--	@docount bit,
--	@username nvarchar (50),
--	@ename nvarchar(50),
--	@set nvarchar(4),
--	@audit nvarchar(4),
--	@cid INT
--)
--as

--set nocount on

----公司Id
--declare @companyid nvarchar(4)
----set @companyid = (select CompanyId from aspnet_Users where username=@username)
--IF(@cid=0)
--SET @companyid = '%'
--ELSE
--	SET @companyid=@cid
----SELECT * FROM employee WHERE CompanyId LIKE '%'

--	if(@docount=1)
--		select ee.*,'' as ENumber,'' as ETempNumber,'' as EName,''as Sex,''as Birthday,''as EducationName,''as IDCard,'' as DepartmentId from employee e inner join employee_entry ee on e.id=ee.eid
--	        where 
--			    e.companyid LIKE @companyid 
--			and e.ename like '%'+@ename+'%'
--			and (case (isnull(ee.tdepartmentid,0)) when 0 then '0' else '1' end) like '%'+@set+'%'
--			and (case when ee.audit=1 then '1' 
--				 when ee.audit=0 and ee.auditperson is null then '0'
--				 when ee.audit=0 and ee.auditperson is not null then '2' end) like '%'+@audit+'%'
--			and  e.state <> 5
			
--	else
--	begin
--	declare @indextable table(id int identity(1,1),nid int)
----	set rowcount @endIndex
--	insert into @indextable(nid) select ee.id from employee e inner join employee_entry ee on e.id=ee.eid
--	        where 
--			    e.companyid LIKE @companyid 
--			and e.ename like '%'+@ename+'%'
--			and (case (isnull(ee.tdepartmentid,0)) when 0 then '0' else '1' end) like '%'+@set+'%'
--			and (case when ee.audit=1 then '1' 
--				 when ee.audit=0 and ee.auditperson is null then '0'
--				 when ee.audit=0 and ee.auditperson is not null then '2' end) like '%'+@audit+'%' 
--			and  e.state <> 5
--		order by e.id asc
--	select 
--		EE.Id,
--		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--		EE.EId,
--		(select EName from Employee E where EE.EId=E.Id) as EName,
--		(select Sex from Employee E where EE.EId=E.Id) as Sex,
--		(select Birthday from Employee E where EE.EId=E.Id) as Birthday,
--		(select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as EducationName,
--		(select IDCard from Employee E where EE.EId=E.Id) as IDCard,
--		EE.TDepartmentId as DepartmentId,
--		(select COUNT(0) from @indextable) as coun,
--		EE.audit,
--		EE.auditPerson,
--		Proposer,
--		(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName

--	from employee_entry EE
--	inner join @indextable t on 
--	EE.id=t.nid
--	where 
--		t.id between @startIndex and @endIndex 
--	order by t.id
--	end
--set nocount off
RETURN

